package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import vo.Student;

public class StudentDAO {
	
	/***
	 * 查找所有记录
	 * @return
	 */
	public List<Student> findAll(){
		List<Student> list=new ArrayList<>();
		String sql="select * from cjb order by stuNo";
		Connection conn = ConnectionFactory.getConnection();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()){
				Student s=new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getInt(5), rs.getInt("english"));
				list.add(s);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/***
	 * 根据主键id查找一条记录
	 * @param id 主键
	 * @return 找到返回一个student对象，否则返回null;
	 */
	public Student findById(int id){
		Student s=null;
		String sql="select * from cjb where id="+id;
		Connection conn = ConnectionFactory.getConnection();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if(rs.next()){
				s=new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getInt(5), rs.getInt(6));
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return s;
	}
	
	public void save(Student s){
		String sql="insert into cjb values(0,?,?,?,?,?)";
		//String sql="insert into cjb values(0,"+s.getStuNo()+","+s.getName()+","+s.getChinese()+","+s.getMath()+","+s.getEnglish()+")";
		String sql2="update cjb set stuNo=?,name=?,chinese=?,math=?,english=? where id=?";
		Connection conn = ConnectionFactory.getConnection();
		try {
			if(s.getId()==0){
				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, s.getStuNo());
				pstmt.setString(2, s.getName());
				pstmt.setInt(3, s.getChinese());
				pstmt.setInt(4, s.getMath());
				pstmt.setInt(5, s.getEnglish());
				pstmt.execute();
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery("select * from cjb order by id desc limit 0,1");
				rs.next();
				s.setId(rs.getInt(1));
			}else{
				PreparedStatement pstmt = conn.prepareStatement(sql2);
				pstmt.setString(1, s.getStuNo());
				pstmt.setString(2, s.getName());
				pstmt.setInt(3, s.getChinese());
				pstmt.setInt(4, s.getMath());
				pstmt.setInt(5, s.getEnglish());
				pstmt.setInt(6, s.getId());
				pstmt.execute();
			}
		} catch (Exception e) {
			System.out.println("保存失败");
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void delete(int id){
		String sql="delete from cjb where id="+id;
		Connection conn = ConnectionFactory.getConnection();
		Statement stmt;
		try {
			stmt = conn.createStatement();
			stmt.execute(sql);
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void delete(Student s){
		int id = s.getId();
		delete(id);
	}
	
}
